Imports System.Data.SqlClient
Imports System.Web
Imports System.Configuration

Public Class Connessione
	
    Private conn As SqlConnection
    Dim err As New cException

	public function getConnection() as SqlConnection 
		
		if conn.State = ConnectionState.Closed then conn.Open()
		return conn
		
	end function
    Public Shared Function stringToDbField(ByVal str As String) As Object
        Dim obj As Object
        If IsNothing(str) Then
            obj = System.DBNull.Value
        Else
            If (str.Trim() = String.Empty) Then
                obj = System.DBNull.Value
            Else
                obj = str.Trim()
            End If
        End If
        Return obj
    End Function


    Private Function getConnString() As String

        Dim ConnString As String = CType(HttpContext.Current.Session("ConnString"), String)
        If ConnString = "" Then
            ConnString = ConfigurationSettings.AppSettings("ConnString")
            HttpContext.Current.Session("ConnString") = ConnString
        End If

        Return ConnString
    End Function

    Public Sub New()
        Dim connstring As String
        If IsNothing(conn) Then
            conn = New SqlConnection(getConnString)
        End If
    End Sub


    Public Sub eseguiSql(ByVal sql As String)
        ' crea il l'oggetto OleDbCommand 
        Dim myCmd As SqlCommand = New SqlCommand(sql, conn)

        If myCmd.Connection.State = ConnectionState.Closed Then _
         myCmd.Connection.Open()
        Try
            myCmd.ExecuteNonQuery()

        Catch ex As Exception
            err.get_exception(ex)
        End Try

        myCmd.Connection.Close()

    End Sub
    Public Sub ExecuteNonQuerySqlCommand(ByVal myCmd As System.Data.SqlClient.SqlCommand)
        ' crea il l'oggetto OleDbCommand 
        myCmd.Connection = conn
        Try
            If myCmd.Connection.State = ConnectionState.Closed Then _
             myCmd.Connection.Open()
            myCmd.ExecuteNonQuery()
        Catch sqlEx As SqlClient.SqlException
            err.get_exception(sqlEx)
        Catch ex As Exception
            err.get_ex(ex)
        Finally
            myCmd.Connection.Close()
        End Try


    End Sub
    Public Function getDataTable(ByVal sqlCmd As String) As DataTable
        Dim myDAdp As SqlDataAdapter = New SqlDataAdapter
        Dim table As New DataTable
        myDAdp.SelectCommand = New SqlCommand(sqlCmd, conn)
        myDAdp.SelectCommand.CommandTimeout = 72000
        Try
            myDAdp.Fill(table)
        Catch ex As Exception
            err.get_exception(ex)
        End Try
        Return table
    End Function

    Public Function getDataSet(ByVal sqlCmd As String) As DataSet
        Dim myDAdp As SqlDataAdapter = New SqlDataAdapter
        Dim ds As New DataSet
        myDAdp.SelectCommand = New SqlCommand(sqlCmd, conn)
        myDAdp.SelectCommand.CommandTimeout = 72000
        Try
            myDAdp.Fill(ds)
        Catch ex As Exception
            err.get_exception(ex)
        End Try
        Return ds
    End Function


    Public Function getValore(ByVal sqlCmd As String) As String
        ' crea l'oggetto sqlDbCommand 
        Dim myCmd As SqlCommand = New SqlCommand(sqlCmd, conn)
        Dim val As Object

        If myCmd.Connection.State = ConnectionState.Closed Then _
         myCmd.Connection.Open()
        Try
            val = myCmd.ExecuteScalar()
        Catch ex As Exception
            err.get_exception(ex)
        End Try

        myCmd.Connection.Close()

        If val Is System.DBNull.Value Then
            Return ""
        Else
            Return CStr(val)
        End If


    End Function


    Public Function eseguiSqlCommandInsertAndGetIdentity(ByVal oCmd As System.Data.SqlClient.SqlCommand) As Integer
        ' crea l'oggetto sqlDbCommand 
        oCmd.Connection = conn
        Dim val As Object

        If oCmd.Connection.State = ConnectionState.Closed Then _
         oCmd.Connection.Open()
        Try
            val = oCmd.ExecuteScalar()
        Catch ex As Exception
            err.get_exception(ex)
        Finally
            oCmd.Connection.Close()
        End Try

        If val Is System.DBNull.Value Then
            Return -1
        Else
            Return CInt(val)
        End If
    End Function
    Public Function eseguiSPInsert(ByVal nomeSp As String) As String
        Dim idF As String

        Dim insertCMD As SqlCommand = New SqlCommand(nomeSp, conn)
        insertCMD.CommandType = CommandType.StoredProcedure

        Dim par As SqlParameter
        par = insertCMD.Parameters.Add("@id", SqlDbType.Int)
        par.Direction = ParameterDirection.Output

        conn.Open()

        Dim insertReader As SqlDataReader = insertCMD.ExecuteReader()

        idF = insertCMD.Parameters("@id").Value

        insertReader.Close()
        conn.Close()

        Return idF

    End Function

    Public Function eseguiSPSelect(ByVal nomeSp As String, ByVal params As SqlParameterCollection) As SqlCommand

        Dim sql As New SqlCommand(nomeSp, conn)
        sql.CommandType = CommandType.StoredProcedure
        sql.CommandTimeout = 72000

        Dim par As SqlParameter

        For Each par In params
            sql.Parameters.Add(par)
        Next


        Return sql
    End Function
End Class
